{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Task-04"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * UNION 等集合运算符通常都会除去重复的记录。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_id, product_name\r\n",
    "  FROM Product\r\n",
    " UNION\r\n",
    "SELECT product_id, product_name\r\n",
    "  FROM Product2;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT  product_id,product_name,product_type\r\n",
    "       ,sale_price,purchase_price\r\n",
    "  FROM PRODUCT \r\n",
    " WHERE sale_price<800\r\n",
    "  \r\n",
    " UNION\r\n",
    " \r\n",
    "SELECT  product_id,product_name,product_type\r\n",
    "       ,sale_price,purchase_price\r\n",
    "  FROM PRODUCT \r\n",
    " WHERE sale_price>1.5*purchase_price;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT  product_id,product_name,product_type\r\n",
    "       ,sale_price,purchase_price\r\n",
    "  FROM product \r\n",
    " WHERE sale_price<800 OR sale_price>1.5*purchase_price;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## UNION 相对 OR 有优势\r\n",
    "### * 要将如果两个不同的表中的结果合并在一起, 就不得不使用 UNION "
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## UNION 的结果中保留重复行\r\n",
    "### 只需要在 UNION 后面添加 ALL 关键字"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 保留重复行\r\n",
    "SELECT product_id, product_name\r\n",
    "  FROM Product\r\n",
    " UNION ALL\r\n",
    "SELECT product_id, product_name\r\n",
    "  FROM Product2;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT * \r\n",
    "  FROM Product \r\n",
    " WHERE sale_price < 1000\r\n",
    " UNION ALL\r\n",
    "SELECT * \r\n",
    "  FROM Product \r\n",
    " WHERE sale_price > 1.5 * purchase_price"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "----合并时，会出现隐式类型转换\r\n",
    "SELECT product_id, product_name, sale_price\r\n",
    "  FROM Product\r\n",
    " UNION\r\n",
    "SELECT product_id, product_name,'1'\r\n",
    "  FROM Product2;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 需要注意的是 hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "----SYSDATE()返回系统时间\r\n",
    "SELECT SYSDATE(), SYSDATE(), SYSDATE()\r\n",
    " \r\n",
    " UNION\r\n",
    " \r\n",
    "SELECT 'chars', 123,  null"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_id, product_name\r\n",
    "  FROM Product\r\n",
    "  \r\n",
    "INTERSECT\r\n",
    "SELECT product_id, product_name\r\n",
    "  FROM Product2"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 用INNER内连接来替换交集"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "\r\n",
    "SELECT p1.product_id, p1.product_name\r\n",
    "  FROM Product p1\r\n",
    "INNER JOIN Product2 p2\r\n",
    "ON p1.product_id=p2.product_id"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 使用 NOT IN 子句的实现方法\r\n",
    "SELECT * \r\n",
    "  FROM Product\r\n",
    " WHERE product_id NOT IN (SELECT product_id \r\n",
    "                            FROM Product2)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT * \r\n",
    "  FROM Product\r\n",
    " WHERE sale_price > 2000 \r\n",
    "   AND product_id NOT IN (SELECT product_id \r\n",
    "                            FROM Product \r\n",
    "                           WHERE sale_price<1.3*purchase_price)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 交INTERSECT\r\n",
    "\r\n",
    "### * 与上面关系一样，表的集合运算可以通过条件的逻辑运算部分替代"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 4.1.5 对称差"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 使用 NOT IN 实现两个表的差集\r\n",
    "SELECT * \r\n",
    "  FROM Product\r\n",
    " WHERE product_id NOT IN (SELECT product_id FROM Product2)\r\n",
    "UNION\r\n",
    "SELECT * \r\n",
    "  FROM Product2\r\n",
    " WHERE product_id NOT IN (SELECT product_id FROM Product)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 4.2 连结(JOIN)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * 问题的关键是, 找出一个类似于\"轴\"或者\"桥梁\"的公共列, 将两张表用这个列连结起来"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 内连结\r\n",
    "-- FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT SP.shop_id\r\n",
    "       ,SP.shop_name\r\n",
    "       ,SP.product_id\r\n",
    "       ,P.product_name\r\n",
    "       ,P.product_type\r\n",
    "       ,P.sale_price\r\n",
    "       ,SP.quantity\r\n",
    "  FROM ShopProduct AS SP\r\n",
    " INNER JOIN Product AS P\r\n",
    "    ON SP.product_id = P.product_id;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * 1.FROM 子句中使用多张表\r\n",
    "## * 2.ON 子句来指定连结条件\r\n",
    "## * 3.指向明确：表名.列名"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "---连接可理解为加大表规模\r\n",
    "SELECT *\r\n",
    "  FROM (-- 第一步查询的结果\r\n",
    "        SELECT SP.shop_id\r\n",
    "               ,SP.shop_name\r\n",
    "               ,SP.product_id\r\n",
    "               ,P.product_name\r\n",
    "               ,P.product_type\r\n",
    "               ,P.sale_price\r\n",
    "               ,SP.quantity\r\n",
    "          FROM ShopProduct AS SP\r\n",
    "         INNER JOIN Product AS P\r\n",
    "            ON SP.product_id = P.product_id) AS STEP1\r\n",
    " WHERE shop_name = '东京'\r\n",
    "   AND product_type = '衣服' ;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "---这样不清晰\r\n",
    "SELECT SP.shop_id\r\n",
    "       ,SP.shop_name\r\n",
    "       ,SP.product_id\r\n",
    "       ,P.product_name\r\n",
    "       ,P.product_type\r\n",
    "       ,P.sale_price\r\n",
    "       ,SP.quantity\r\n",
    "  FROM ShopProduct AS SP\r\n",
    " INNER JOIN Product AS P\r\n",
    "    ON (SP.product_id = P.product_id\r\n",
    "   AND SP.shop_name = '东京'\r\n",
    "   AND P.product_type = '衣服') ;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT SP.shop_id\r\n",
    "       ,SP.shop_name\r\n",
    "       ,SP.product_id\r\n",
    "       ,P.product_name\r\n",
    "       ,P.product_type\r\n",
    "       ,P.sale_price\r\n",
    "       ,SP.quantity\r\n",
    "  FROM (-- 子查询 1:从 ShopProduct 表筛选出东京商店的信息\r\n",
    "        SELECT *\r\n",
    "          FROM ShopProduct\r\n",
    "         WHERE shop_name = '东京' ) AS SP\r\n",
    " INNER JOIN -- 子查询 2:从 Product 表筛选出衣服类商品的信息\r\n",
    "   (SELECT *\r\n",
    "      FROM Product\r\n",
    "     WHERE product_type = '衣服') AS P\r\n",
    "    ON SP.product_id = P.product_id;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 4.2.1.3 结合 GROUP BY 子句使用内连结"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 参考答案\r\n",
    "SELECT SP.shop_id\r\n",
    "      ,SP.shop_name\r\n",
    "      ,MAX(P.sale_price) AS max_price\r\n",
    "  FROM shopproduct AS SP\r\n",
    " INNER JOINproduct AS P\r\n",
    "    ON SP.product_id = P.product_id\r\n",
    " GROUP BY SP.shop_id,SP.shop_name"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 4.2.1.4 自连结(SELF JOIN)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "--------子查询\r\n",
    "SELECT product_type, product_name, sale_price\r\n",
    "  FROM product AS P1\r\n",
    " WHERE sale_price > (SELECT AVG(sale_price)\r\n",
    "                       FROM product AS P2\r\n",
    "                      WHERE P1.product_type = P2.product_type\r\n",
    "                      GROUP BY product_type);"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT  product_type\r\n",
    "       ,AVG(sale_price) AS avg_price \r\n",
    "  FROM Product \r\n",
    " GROUP BY product_type;\r\n",
    " ----内连接实现\r\n",
    "SELECT  P1.product_id\r\n",
    "       ,P1.product_name\r\n",
    "       ,P1.product_type\r\n",
    "       ,P1.sale_price\r\n",
    "       ,P2.avg_price\r\n",
    "  FROM Product AS P1 \r\n",
    " INNER JOIN \r\n",
    "   (SELECT product_type,AVG(sale_price) AS avg_price \r\n",
    "      FROM Product \r\n",
    "     GROUP BY product_type) AS P2 \r\n",
    "    ON P1.product_type = P2.product_type;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "------替换子查询\r\n",
    "\r\n",
    "SELECT  P1.product_id\r\n",
    "       ,P1.product_name\r\n",
    "       ,P1.product_type\r\n",
    "       ,P1.sale_price\r\n",
    "       ,P2.avg_price\r\n",
    "  FROM Product AS P1\r\n",
    " INNER JOIN \r\n",
    "   (SELECT product_type,AVG(sale_price) AS avg_price \r\n",
    "      FROM Product \r\n",
    "     GROUP BY product_type) AS P2 \r\n",
    "    ON P1.product_type = P2.product_type\r\n",
    " WHERE P1.sale_price > P2.avg_price;\r\n",
    " ---思路实际上更加清晰"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 4.2.1.6 自然连结(NATURAL JOIN)\r\n",
    "\r\n",
    "### * 当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT *  FROM shopproduct NATURAL JOIN Product"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "\r\n",
    "SELECT  SP.product_id,SP.shop_id,SP.shop_name,SP.quantity\r\n",
    "       ,P.product_name,P.product_type,P.sale_price\r\n",
    "       ,P.purchase_price,P.regist_date  \r\n",
    "  FROM shopproduct AS SP \r\n",
    " INNER JOIN Product AS P \r\n",
    "    ON SP.product_id = P.product_id"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT * FROM Product NATURAL JOIN Product2;\r\n",
    "SELECT * \r\n",
    "  FROM (SELECT product_id, product_name\r\n",
    "          FROM Product ) AS A \r\n",
    "NATURAL JOIN \r\n",
    "   (SELECT product_id, product_name \r\n",
    "      FROM Product2) AS B;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "---求交集\r\n",
    "SELECT P1.*\r\n",
    "  FROM Product AS P1\r\n",
    " INNER JOIN Product2 AS P2\r\n",
    "    ON (P1.product_id  = P2.product_id\r\n",
    "   AND P1.product_name = P2.product_name\r\n",
    "   AND P1.product_type = P2.product_type\r\n",
    "   AND P1.sale_price   = P2.sale_price\r\n",
    "   AND P1.regist_date  = P2.regist_date)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT P1.*\r\n",
    "  FROM Product AS P1\r\n",
    " INNER JOIN Product2 AS P2\r\n",
    "    ON P1.product_id = P2.product_id"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 4.2.2 外连结(OUTER JOIN)\r\n",
    "\r\n",
    "### * 外连结会根据外连结的种类有选择地保留无法匹配到的行。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 左连结     \r\n",
    "FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>\r\n",
    "-- 右连结     \r\n",
    "FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>\r\n",
    "-- 全外连结\r\n",
    "FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT SP.shop_id\r\n",
    "       ,SP.shop_name\r\n",
    "       ,SP.product_id\r\n",
    "       ,P.product_name\r\n",
    "       ,P.sale_price\r\n",
    "  FROM Product AS P\r\n",
    "  LEFT OUTER JOIN ShopProduct AS SP\r\n",
    "    ON SP.product_id = P.product_id;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## ●  外连结要点 1: 选取出单张表中全部的信息\r\n",
    "\r\n",
    "与内连结的结果相比,不同点显而易见,那就是结果的行数不一样.内连结的结果中有 13 条记录,而外连结的结果中有 15 条记录,增加的 2 条记录到底是什么呢?这正是外连结的关键点. 多出的 2 条记录是高压锅和圆珠笔,这 2 条记录在 ShopProduct 表中并不存在,也就是说,这 2 种商品在任何商店中都没有销售.由于内连结只能选取出同时存在于两张表中的数据,因此只在 Product 表中存在的 2 种商品并没有出现在结果之中.相反,对于外连结来说,只要数据存在于某一张表当中,就能够读取出来.在实际的业务中,例如想要生成固定行数的单据时,就需要使用外连结.如果使用内连结的话,根据 SELECT 语句执行时商店库存状况的不同,结果的行数也会发生改变,生成的单据的版式也会受到影响,而使用外连结能够得到固定行数的结果.虽说如此,那些表中不存在的信息我们还是无法得到,结果中高压锅和圆珠笔的商店编号和商店名称都是 NULL （具体信息大家都不知道,真是无可奈何）.外连结名称的由来也跟 NULL 有关,即“结果中包含原表中不存在（在原表之外）的信息”.相反,只包含表内信息的连结也就被称为内连结了。\r\n",
    "\r\n",
    "## ● 外连结要点 2：使用 LEFT、RIGHT 来指定主表.\r\n",
    "\r\n",
    "外连结还有一点非常重要,那就是要把哪张表作为主表.最终的结果中会包含主表内所有的数据.指定主表的关键字是 LEFT 和 RIGHT.顾名思义,使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表.代码清单 7-11 中使用了 RIGHT ,因此,右侧的表,也就是 Product 表是主表.我们还可以像代码清单 7-12 这样进行改写,意思完全相同.这样你可能会困惑，到底应该使用 LEFT 还是 RIGHT？其实它们的功能没有任何区别,使用哪一个都可以.通常使用 LEFT 的情况会多一些,但也并没有非使用这个不可的理由,使用 RIGHT 也没有问题。\r\n",
    "\r\n",
    "通过交换两个表的顺序, 同时将 LEFT 更换为 RIGHT(如果原先是 RIGHT,则更换为 LEFT), 两种方式会到完全相同的结果。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT P.product_id\r\n",
    "       ,P.product_name\r\n",
    "       ,P.sale_price\r\n",
    "       ,SP.shop_id\r\n",
    "       ,SP.shop_name\r\n",
    "       ,SP.quantity\r\n",
    "  FROM Product AS P\r\n",
    "  LEFT OUTER JOIN ShopProduct AS SP\r\n",
    "    ON SP.product_id = P.product_id\r\n",
    " WHERE quantity< 50"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT P.product_id\r\n",
    "      ,P.product_name\r\n",
    "      ,P.sale_price\r\n",
    "       ,SP.shop_id\r\n",
    "      ,SP.shop_name\r\n",
    "      ,SP.quantity \r\n",
    "  FROM Product AS P\r\n",
    "  LEFT OUTER JOIN-- 先筛选quantity<50的商品\r\n",
    "   (SELECT *\r\n",
    "      FROM ShopProduct\r\n",
    "     WHERE quantity < 50 ) AS SP\r\n",
    "    ON SP.product_id = P.product_id"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 4.2.5 交叉连结—— CROSS JOIN(笛卡尔积)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT  P1.product_id\r\n",
    "       ,P1.product_name\r\n",
    "       ,P1.sale_price\r\n",
    "       ,P2.product_id AS P2_id\r\n",
    "       ,P2.product_name AS P2_name\r\n",
    "       ,P2.sale_price AS P2_price \r\n",
    "  FROM Product AS P1 \r\n",
    "  LEFT OUTER JOIN Product AS P2 \r\n",
    "    ON P1.sale_price >= P2.sale_price\r\n",
    " ORDER BY P1.sale_price,P1.product_id\t"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 练习题"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT  product_id,product_name,product_type\r\n",
    "       ,sale_price,purchase_price\r\n",
    "  FROM PRODUCT \r\n",
    " WHERE sale_price > 500\r\n",
    "  \r\n",
    " UNION\r\n",
    " \r\n",
    "SELECT  product_id,product_name,product_type\r\n",
    "       ,sale_price,purchase_price\r\n",
    "  FROM PRODUCT2 \r\n",
    " WHERE sale_price > 500;\r\n",
    "  "
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 使用 IN 实现两个表的差集\r\n",
    "-- 对称差求交集\r\n",
    "SELECT * \r\n",
    "  FROM Product\r\n",
    " WHERE product_id  IN (SELECT product_id FROM Product2)\r\n",
    "UNION\r\n",
    "SELECT * \r\n",
    "  FROM Product2\r\n",
    " WHERE product_id  IN (SELECT product_id FROM Product)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 获取每个商店里售价最高的商品的名称和售价?"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-----好像不太对啊；\r\n",
    "SELECT SP.shop_id\r\n",
    "      ,SP.shop_name\r\n",
    "      ,MAX(P.sale_price) AS max_price\r\n",
    "      ,p.product_name\r\n",
    "      ,P.product_id\r\n",
    "  FROM shopproduct AS SP\r\n",
    " INNER JOIN product AS P\r\n",
    "    ON SP.product_id = P.product_id\r\n",
    " GROUP BY SP.shop_id,SP.shop_name;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "------关联子查询你，查最大值\r\n",
    "SELECT product_type, product_name, sale_price\r\n",
    "  FROM product AS P1\r\n",
    " WHERE sale_price >= (SELECT MAX(sale_price)\r\n",
    "                       FROM product AS P2\r\n",
    "                      WHERE P1.product_type = P2.product_type\r\n",
    "                      GROUP BY product_type);"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "---内连结查最大值\r\n",
    "SELECT  P1.product_id\r\n",
    "       ,P1.product_name\r\n",
    "       ,P1.product_type\r\n",
    "       ,P1.sale_price\r\n",
    "       ,P2.max_price\r\n",
    "  FROM Product AS P1\r\n",
    " INNER JOIN \r\n",
    "   (SELECT product_type,MAX(sale_price) AS max_price \r\n",
    "      FROM Product \r\n",
    "     GROUP BY product_type) AS P2 \r\n",
    "    ON P1.product_type = P2.product_type---按此分组\r\n",
    " WHERE P1.sale_price >= P2.max_price; ---找P1中对应的最大值"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_type, product_name, sale_price,\r\n",
    "    (SELECT SUM(sale_price)\r\n",
    "    FROM product AS P2\r\n",
    "    WHERE P1.product_type = P2.product_type\r\n",
    "    GROUP BY product_type) AS sum_price\r\n",
    "  FROM product AS P1\r\n",
    "ORDER BY sale_price;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# EDN   Task-04        2021-8-25"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [],
   "metadata": {}
  }
 ],
 "metadata": {
  "orig_nbformat": 4,
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}